/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.CountryDao;
import com.tesam.gedeon.entity.Country;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Sistema
 */
@Repository
public class CountryDaoImpl extends SimpleJdbcDaoSupport implements CountryDao {

    @Autowired
    public CountryDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }
    @Override
    public List<Country> getListAll() {
      List<Country> result = new ArrayList();
      try{ 
          result = getJdbcTemplate().query("select code , id , longcode as longCode , name from country where deleted = 0 order by name asc  ",
                  new BeanPropertyRowMapper<Country>(Country.class) ) ;
   
      }catch(Exception e){
      System.out.println("getListAll : "+e.getMessage());
      }
      
      
      return result ; 
    }

    @Override
    public int getCountAll() {
        return getJdbcTemplate().queryForInt("select count(*) as total from country where deleted = 0 ");
    }

    @Override
    public List<Country> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        List<Country> result = new ArrayList();
        try{
            String sql = " select code , longcode as longCode , name , id from country "
                    + "where deleted = 0 "
                    + "order by "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit ;
            
            result = getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Country>(Country.class));
        }catch(Exception e){
        
        }
        return result ;
    }

    @Override
    public List<Country> getSearchCountryByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder, Country country) {
       List<Country> result = new ArrayList();
        try{
          String sql = " select code , longcode as longCode , name , id from country "
                    + "where deleted = 0 and (upper(code) || ' ' || upper(longcode) || ' ' || upper(name)) like '%"+country.getName().toUpperCase()+"%' "
                    + "order by "+dataToSort+" "+directionOfOrder+" limit "+endLimit+" offset "+startLimit ;
           
          result = getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Country>(Country.class)   );
        }catch(Exception e){
        
        }
       return result ;
    }

    @Override
    public List<Country> getCountryById(Country country) {
     List<Country> result = new ArrayList();
     
     try{ 
         
         getJdbcTemplate().update("update country set viewedtime = NOW() where id = ? ", country.getId());
         
     result = getJdbcTemplate().query("select code , longcode as longCode , name , id from country "
             + " where id = ?",new BeanPropertyRowMapper<Country>(Country.class) , country.getId());
     }catch(Exception e){
     
     }
     return result ;
    }

    @Override
    public void updateCountry(Country country) {
        
        try{
            String sql = "update country set code = ? , longcode = ? , name = ? "
                    + " where id = ?" ;
              
            getJdbcTemplate().update(sql, country.getCode() , country.getLongCode() , country.getName() ,
                    country.getId());
            
        }catch(Exception e){
        
        }
         
    }

    @Override
    public int saveCountry(Country country) {
       int id = 0 ;
            try{
                String sql = "insert into country "
                        + "(createdby , modifiedby , datecreated , status , deleted , presence ,  "
                        + "ownerid , code , longcode , name ) " +
                        "values(? , ? , NOW() , 1 , 0 , 0 , "
                        + "? , ? , ? , ? )" ;
             
                getJdbcTemplate().update(sql , country.getCreatedBy() , country.getModifiedBy()  ,
                                country.getOwnerId() , country.getCode() , country.getLongCode() , country.getName());
                
                 id = getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
                 
                
            }catch(Exception e){
            
            }
       return id ; 
    }

    @Override
    public List<Country> deleteCountry(Country country) {
        List<Country> result = new ArrayList();
        
            try{
            getSimpleJdbcTemplate().update("update country set deleted = 1 where id = ?" , country.getId());
            
            result = this.getAllByLimit(0, 20, "name", "asc") ;
            
            }catch(Exception e){
            
            }
        
        return result ;
    }

    @Override
    public int getTotalBySearch(Country country) {
     return getJdbcTemplate().queryForInt("select count(*) as total from country where deleted = 0  "
             + "  and (upper(code) || ' ' || upper(longcode) || ' ' || upper(name)) like '%"+country.getName().toUpperCase()+"%' ");
    }
    
     private String getLimit(String query, int startLimit, int endLimit,
            String dataToSort, String directionOfOrder) {

        StringBuilder newQuery = new StringBuilder(query);

        newQuery.append(" ORDER BY ");
        newQuery.append(dataToSort);
        newQuery.append(" ");
        newQuery.append(directionOfOrder);
        newQuery.append(" ");
        newQuery.append("LIMIT ");
        newQuery.append(endLimit);
        newQuery.append(" OFFSET ");
        newQuery.append(startLimit);

        return newQuery.toString();
    }
    
}

